import xlrd
import datetime
from diary_archive.submitDB import MySQLOperator
from diary_archive.submit_clickhouse import CkOperator


class DiaryReader:
    def __init__(self):
        self.file_name = "E:/同步盘/华为家庭存储/张文鹤资料/002_个人管理/每日总结/2022/每日记录202211.xls"
        self.sheet_name = "11月"
        self.bk = xlrd.open_workbook(self.file_name)
        self.sh = self.bk.sheet_by_name(self.sheet_name)
        self.nrows = self.sh.nrows  # 获取行数
        self.type_list = {"01": "工作", "02": "个人", "03": "学习", "04": "家庭", "05": "交际", "06": "交通",
                          "07": "杂项", "08": "娱乐"}
        self.type_list_convert = {value: key for key, value in self.type_list.items()}
        self.subtype_list = {
            "0101": "本职工作", "0102": "同事交流", "0103": "会议", "0104": "额外工作", "0105": "私人工作", "0106": "协助李丹",
            "0107": "客户交流", "0108": "工作汇报", "0201": "思路梳理", "0202": "跑步/运动", "0203": "阅读/写作", "0204": "每日总结",
            "0205": "思考", "0206": "散步", "0207": "资料整理", "0301": "技术学习", "0302": "技术研究", "0303": "技术阅读",
            "0304" : "英语学习", "0305": "修养", "0306": "历史", "0401": "家务", "0402": "陪伴家人", "0403": "照顾孩子",
            "0404": "沟通", "0501":"朋友","0502": "同事", "0503": "同学", "0504": "技术圈", "0505": "其他", "0601": "飞机",
            "0602": "火车", "0603": "公交地铁", "0604": "汽车", "0605": "步行/骑行", "0701": "吃饭", "0702": "洗漱/个人卫生",
            "0703": "购物", "0704": "求职", "0705": "整理", "0706": "个人事务", "0707": "睡眠", "0801": "游戏", "0802": "上网",
            "0803": "电影电视", "0804": "旅游", "0805": "爱好", "0806": "其他"
        }
        self.subtype_list_convert = {value: key for key, value in self.subtype_list.items()}

        self.boolean_list = {
            "是": '1', "否": "0"
        }
        self.plan_list = {
            "内": "1", "外": "0"
        }
        self.priority_list = {
            "重要紧急": "1", "重要不紧急": "2", "不重要紧急": "3", "不重要不紧急": "4"
        }

    def date_convertor(self, dates):  # 定义转化日期戳的函数,dates为日期戳
        delta = datetime.timedelta(days=dates)
        today = datetime.datetime.strptime('1899-12-30', '%Y-%m-%d') + delta  # 将1899-12-30转化为可以计算的时间格式并加上要转化的日期戳
        return datetime.datetime.strftime(today, '%Y-%m-%d')  # 制定输出日期的格式

    def time_convertor(self, time):
        if time == 1.0:
            return "23:59:59"

        time_tuple = xlrd.xldate.xldate_as_tuple(time, 0)
        hour = time_tuple[3]
        minute = time_tuple[4]
        second = time_tuple[5]
        return str(hour) + ":" + str(minute) + ":00"

    def get_today_num(self, dates):
        start = 0
        end = 0
        for i in range(1, self.nrows):
            if dates == self.sh.cell_value(i, 0):
                start = i
        for i in range(start+1, self.nrows):
            if not isinstance(self.sh.cell_value(i, 0), str) or self.sh.cell_value(i, 0) == 'END':
                end = i
                break
        return start, end

    def read_columns(self, column):

        str_list = []
        for i in range(1, self.nrows):
            # row_data = sh.row_values(i)
            # print(int(sh.cell_value(i, column)))
            # if not sh.cell_value(i, column) is '' and sh.cell_value(i, column) is 'END':
            if not isinstance(self.sh.cell_value(i, 0), str):
                str_list.append(self.sh.cell_value(i, column))
        return str_list

    def read_content(self, start_end):
        list_task = []
        date = self.sh.cell_value(start_end[0], 0)
        date_str = self.date_convertor(int(date))
        for i in range(start_end[0], start_end[1]):
            task_name = self.sh.cell_value(i, 1)
            # print(task_name)
            task_type = self.sh.cell_value(i, 2)
            task_type_str = self.type_list_convert.get(task_type)
            task_subtype = self.sh.cell_value(i, 3)
            task_subtype_str = self.subtype_list_convert.get(task_subtype)
            task_content = self.sh.cell_value(i, 4)
            task_import_flg = self.sh.cell_value(i, 5)
            task_import_flg_str = self.boolean_list.get(task_import_flg)
            task_plan_flg = self.sh.cell_value(i, 6)
            task_plan_flg_str = self.plan_list.get(task_plan_flg)
            task_priority = self.sh.cell_value(i, 7)
            task_priority_str = self.priority_list.get(task_priority)
            task_start = self.time_convertor(self.sh.cell_value(i, 9))
            task_start_str = date_str + " " + task_start
            task_end = self.time_convertor(self.sh.cell_value(i, 10))
            task_end_str = date_str + " " + task_end
            task_gain = self.sh.cell_value(i, 11)
            task_time = self.sh.cell_value(i, 8)

            data_content = (date_str, task_name, task_type_str, task_subtype_str, task_content,
                            task_import_flg_str, task_plan_flg_str, task_priority_str, task_start_str, task_end_str,
                            task_gain, task_time)
            list_task.append(data_content)
        return list_task

    def get_all_items(self):
        # print(reader.date_convertor(43830.0))
        # start_end = reader.get_today_num(43828.0)
        # print(start_end)
        name_list = self.read_columns(0)
        res_list = []
        for name in name_list:
            start_end = reader.get_today_num(name)
            list = reader.read_content(start_end)
            res_list.extend(list)
        return res_list


if __name__ == '__main__':

    reader = DiaryReader()
    res_list = reader.get_all_items()
    # print(reader.date_convertor(43830.0))
    # start_end = reader.get_today_num(43828.0)
    # print(start_end)
    # name_list = reader.read_columns(0)
    # res_list = []
    # for name in name_list:
    #     start_end = reader.get_today_num(name)
    #     print(start_end)
    #     list = reader.read_content(start_end)
    #     res_list.extend(list)
    # print(res_list)

    #      print(start_end[0])
    # print(start_end[1])
    # 写入MySQL DB
    # mydb = MySQLOperator()
    # for item in res_list:
    #     # print(item)
    #     mydb.insert_item(item)
    # mydb.close()
    ckop = CkOperator()
    ckop.batch_insert(res_list)
    print("finish!")
